#!/bin/bash

dbname=$1

# help
echo "HELP;" | ./thisadb $dbname

# open query plans
echo "QUERYPLANS ON;" | ./thisadb $dbname

# select examples
echo "SELECT * FROM REGION;" | ./thisadb $dbname
echo "SELECT * FROM NATION WHERE N_REGIONKEY = 1;" | ./thisadb $dbname
echo "SELECT P_NAME, P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT FROM PART WHERE P_RETAILPRICE <= 950.0;" | ./thisadb $dbname
echo "SELECT SUPPLIER.S_NAME, SUPPLIER.S_PHONE FROM SUPPLIER WHERE S_NATIONKEY = 1;" | ./thisadb $dbname

# select join
echo "SELECT NATION.N_NAME, CUSTOMER.C_NAME FROM NATION, CUSTOMER WHERE NATION.N_NATIONKEY = CUSTOMER.C_NATIONKEY;" | ./thisadb $dbname
echo "SELECT NATION.NATIONKEY, NATION.N_NAME, CUSTOMER.C_NAME FROM NATION, CUSTOMER WHERE NATION.N_NATIONKEY = CUSTOMER.C_NATIONKEY AND NATION.N_NATIONKEY = 1;" | ./thisadb $dbname
echo "SELECT NATION.NATIONKEY, NATION.N_NAME, CUSTOMER.C_NAME, CUSTOMER.C_ACCTBAL FROM NATION, CUSTOMER WHERE NATION.N_NATIONKEY = CUSTOMER.C_NATIONKEY AND NATION.N_NATIONKEY = 1 AND C_ACCTBAL<1000.0;" | ./thisadb $dbname
# echo "SELECT NATION.N_NAME, CUSTOMER.C_NAME, ORDERS.O_TOTALPRICE FROM NATION, CUSTOMER, ORDERS WHERE NATION.N_NATIONKEY = CUSTOMER.C_NATIONKEY AND CUSTOMER.C_CUSTKEY = ORDERS.O_CUSTKEY;" | ./thisadb $dbname

# select group by
echo "SELECT O_CUSTKEY, MIN(O_TOTALPRICE) FROM ORDERS GROUP BY O_CUSTKEY;" | ./thisadb $dbname

# select order by
echo "SELECT * FROM NATION ORDER BY N_REGIONKEY;" | ./thisadb $dbname
echo "SELECT * FROM NATION ORDER BY N_REGIONKEY DESC;" | ./thisadb $dbname
echo "SELECT * FROM NATION ORDER BY N_REGIONKEY ASC;" | ./thisadb $dbname

# insert example
echo "INSERT INTO NATION VALUES (25, \"ABC\", 1, \"ABCDEFGH\");" | ./thisadb $dbname

# update example
echo "UPDATE NATION SET N_NAME=\"ICELAND\" WHERE N_NATIONKEY=25;" | ./thisadb $dbname

# delete example
echo "DELETE FROM NATION WHERE N_NATIONKEY=25;" | ./thisadb $dbname



